import pandas as pd
import numpy as np


df2=pd.read_excel('非洲通讯产品销售数据.xlsx',sheet_name='SalespersonData')
df2=df2.iloc[:,[1,3,4]]
name=df2['销售经理'].unique()
df2['销售合同数']=df2['销售合同']/df2['成交率']
for i in name:
    df2.loc[df2['销售经理']==i,'成交合同数']= df2.loc[df2['销售经理']==i,'销售合同'].cumsum()
    df2.loc[df2['销售经理']==i,'销售合同总数']= df2.loc[df2['销售经理']==i,'销售合同数'].cumsum()
df2=df2.drop_duplicates(subset=['销售经理'],keep='last')
df2['成交率']=df2['成交合同数']/df2['销售合同总数']
df2=df2.iloc[:,[0,2,4]]

df2['成交率']=df2['成交率'].apply(lambda x:format(x,'.1%'))
df2.to_excel('task1_3.xlsx',index=False)


